package com.codegero.android.client.sms.gateway.stored;

import java.util.ArrayList;
import java.util.List;

import com.codegero.android.client.sms.gateway.entity.RegistryVote;
import com.codegero.android.client.sms.gateway.entity.Response;
import com.codegero.android.client.sms.gateway.entity.Vote;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseManager {
	
	public DatabaseManager (Context context) { 
		mDatabaseHelper = new DatabaseHelper(context);
	}
	
	public void deleteRegistryVote(RegistryVote registryVote){
		getDb().delete
			(REGISTRY_VOTE_TABLE_NAME, COLUMN_REGISTRY_VOTE_ID + "=" + registryVote.getId(), null);
	}
	
	public void deleteResponse(Response response){
		getDb().delete
			(RESPONSE_TABLE_NAME, COLUMN_RESPONSE_ID + "=" + response.getId(), null);
	}
	
	public void deleteVote(Vote vote){
		getDb().delete
			(VOTE_TABLE_NAME, COLUMN_VOTE_ID + "=" + vote.getId(), null);
	}
	
	public void updateRegistryVote(RegistryVote registryVote){
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_REGISTRY_VOTE_NAME, registryVote.getNama());
		getDb().update(REGISTRY_VOTE_TABLE_NAME, 
	            cv,
	            COLUMN_REGISTRY_VOTE_ID + "=" + registryVote.getId(), 
	            null);
	}
	
	public void updateResponse(Response response){
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_RESPONSE_MESSAGE, response.getMessage());
		getDb().update(RESPONSE_TABLE_NAME, 
	            cv,
	            COLUMN_RESPONSE_ID + "=" + response.getId(), 
	            null);
	}
	
	public void addVote(Vote vote){
		if(vote!=null){
			ContentValues cv = new ContentValues();
			cv.put(COLUMN_VOTE_ID, vote.getId());
			cv.put(COLUMN_VOTE_NO_HP, vote.getNoHp());
			cv.put(COLUMN_VOTE_MESSAGE, vote.getMessage());
			getDb().insert(VOTE_TABLE_NAME, null, cv);	
		}
	}
	
	
	
	public void addResponse(Response response){
		if(response!=null){
			ContentValues cv = new ContentValues();
			cv.put(COLUMN_RESPONSE_ID, response.getId());
			cv.put(COLUMN_RESPONSE_MESSAGE, response.getMessage());
			getDb().insert(RESPONSE_TABLE_NAME, null, cv);	
		}
	}
	
	public void addRegistryVote(RegistryVote registryVote){
		if(registryVote!=null){
			ContentValues cv = new ContentValues();
			cv.put(COLUMN_REGISTRY_VOTE_ID, registryVote.getId());
			cv.put(COLUMN_REGISTRY_VOTE_NAME, registryVote.getNama());
			getDb().insert(VOTE_TABLE_NAME, null, cv);	
			// TODO : daftar yang votes......
		}
	}
	
	public List<Vote> getVotesById(String id){
		Cursor cursor = actionQuery(VOTE_TABLE_NAME, null, COLUMN_VOTE_ID+"='"+id+"'");
		if(cursor!=null && cursor.getCount()>0){
			return getVotesFromCursor(cursor);
		}else{
			return null;
		}
	}
	
	public List<Response> getResponseById(String id){
		Cursor cursor = actionQuery(RESPONSE_TABLE_NAME, null, COLUMN_RESPONSE_ID+"='"+id+"'");
		if(cursor!=null && cursor.getCount()>0){
			return getResponsesFromCursor(cursor);
		}else{
			return null;
		}
	}
	
	public List<Response> getAllResponses(){
		Cursor cursor = 
				actionQuery(RESPONSE_TABLE_NAME, null, null);
		Log.d("database", "cursorcountis"+cursor.getCount());
		if(cursor!=null && cursor.getCount()>0){
			return getResponsesFromCursor(cursor);
		}else{
			return null;
		}
	}
	
	public List<RegistryVote> getAllRegistryVotes(boolean withVote){
		Cursor cursor = 
				actionQuery(REGISTRY_VOTE_TABLE_NAME, null, null);
		if(cursor!=null && cursor.getCount()>0){
			return getRegistryVotesFromCursor(cursor, withVote);
		}else{
			return null;
		}
	}
	
	public List<Vote> getAllVotes(){
		Cursor cursor = 
				actionQuery(RESPONSE_TABLE_NAME, null, null);
		if(cursor!=null && cursor.getCount()>0){
			return getVotesFromCursor(cursor);
		}else{
			return null;
		}
	}
	
	private List<RegistryVote> getRegistryVotesFromCursor(Cursor cursor, boolean withVote){
		List<RegistryVote> votes = new ArrayList<RegistryVote>();
		if(cursor.moveToFirst()){
			do{
				 votes.add(getRegistryVoteFromCursor(cursor, withVote));
			}while(cursor.moveToNext());
		}
		cursor.close();
		return votes;
	}
	
	private List<Response> getResponsesFromCursor(Cursor cursor){
		List<Response> responses = new ArrayList<Response>();
		if(cursor.moveToFirst()){
			do{
				 responses.add(getResponseFromCursor(cursor));
			}while(cursor.moveToNext());
		}
		cursor.close();
		return responses;
	}
	
	private List<Vote> getVotesFromCursor(Cursor cursor){
		List<Vote> votes = new ArrayList<Vote>();
		if(cursor.moveToFirst()){
			do{
				 votes.add(getVoteFromCursor(cursor));
			}while(cursor.moveToNext());
		}
		cursor.close();
		return votes;
	}
	

	private RegistryVote getRegistryVoteFromCursor(Cursor cursor, boolean withVote){
		
		if(cursor!=null && cursor.getCount()>0){
			
			RegistryVote vote = new RegistryVote();
			 
			int indexId 		= cursor.getColumnIndex(COLUMN_REGISTRY_VOTE_ID);
			int indexName		= cursor.getColumnIndex(COLUMN_REGISTRY_VOTE_NAME);
			 
			vote.setId(cursor.getString(indexId));
			vote.setNama(cursor.getString(indexName));
			if(withVote){
				vote.setVotes(getVotesById(cursor.getString(indexId)));
			}
			return vote;
		}
		return null;
		
	}
	
	private Response getResponseFromCursor(Cursor cursor){
		
		if(cursor!=null && cursor.getCount()>0){
			
			Response response = new Response();
			 
			int indexId 		= cursor.getColumnIndex(COLUMN_RESPONSE_ID);
			int indexMessage	= cursor.getColumnIndex(COLUMN_RESPONSE_MESSAGE);
			 
			response.setId(cursor.getString(indexId));
			response.setMessage(cursor.getString(indexMessage));
			return response;
		}
		return null;
		
	}
	
	private Vote getVoteFromCursor(Cursor cursor){
		
		if(cursor!=null && cursor.getCount()>0){
			
			Vote vote = new Vote();
			 
			int indexId 		= cursor.getColumnIndex(COLUMN_VOTE_ID);
			int indexNoHp		= cursor.getColumnIndex(COLUMN_VOTE_NO_HP);
			int indexMessage	= cursor.getColumnIndex(COLUMN_VOTE_MESSAGE);
			 
			vote.setId(cursor.getString(indexId));
			vote.setNoHp(cursor.getInt(indexNoHp));
			vote.setMessage(cursor.getString(indexMessage));
			return vote;
		}
		return null;
		
	}
	
	private Cursor actionQuery(String table, String[]columns, String selection){
		  
		SQLiteDatabase db 	= getDb();
		final Cursor cursor = 
				db.query(table, columns, selection, null, null, null, null, null);
		db = null;
		return cursor;
	}
	
	private SQLiteDatabase getDb(){
		return mDatabaseHelper.getWritableDatabase();
	}
	
	private class DatabaseHelper extends SQLiteOpenHelper{
		
	    
	    public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
	
	    private void createTables(SQLiteDatabase db){
	    	db.execSQL(CREATE_REGISTRY);;
	    	db.execSQL(CREATE_RESPONSE);
			db.execSQL(CREATE_VOTE);
	    }
	    
	    private void dropTables(SQLiteDatabase db){
	    	db.execSQL("DROP TABLE IF EXISTS " + RESPONSE_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + VOTE_TABLE_NAME);
			
	    }
	    
		@Override
		public void onCreate(SQLiteDatabase db) {
			createTables(db);
		}
	
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			dropTables(db);
			createTables(db);
		}
		
		private static final String DATABASE_NAME = "gebros.db";
	    private static final int DATABASE_VERSION = 1;
		
	    private static final String CREATE_REGISTRY = 
	    		"CREATE TABLE IF NOT EXISTS "+
	    		REGISTRY_VOTE_TABLE_NAME+" ("+
	    		COLUMN_REGISTRY_VOTE_ID+" VARCHAR(10) PRIMARY KEY,"+
	    		COLUMN_REGISTRY_VOTE_NAME+" VARCHAR(100))";
	    
	    private static final String CREATE_VOTE = 
	    		"CREATE TABLE IF NOT EXISTS "+
	    		VOTE_TABLE_NAME+" ("+
	    		COLUMN_VOTE_ID+" VARCHAR(10) PRIMARY KEY,"+
	    		COLUMN_VOTE_NO_HP+" INTEGER,"+
	    		COLUMN_VOTE_MESSAGE+" VARCHAR(100))";
	    
	    private static final String CREATE_RESPONSE = 
	    		"CREATE TABLE IF NOT EXISTS "+
	    		RESPONSE_TABLE_NAME+" ("+
	    		COLUMN_RESPONSE_ID+" VARCHAR(10) PRIMARY KEY,"+
	    		COLUMN_RESPONSE_MESSAGE+" VARCHAR(100))";
	   
	    
	}
	
	private DatabaseHelper mDatabaseHelper = null;
	
	private static final String RESPONSE_TABLE_NAME	=
    		"response";
    private static final String COLUMN_RESPONSE_ID		= "id";
    private static final String COLUMN_RESPONSE_MESSAGE	= "message";

    private static final String REGISTRY_VOTE_TABLE_NAME = 
    		"registry";
    private static final String COLUMN_REGISTRY_VOTE_ID		= "id";
    private static final String COLUMN_REGISTRY_VOTE_NAME	= "name";
    
    private static final String VOTE_TABLE_NAME		=
    		"vote";
    private static final String COLUMN_VOTE_ID		= "id";
    private static final String COLUMN_VOTE_NO_HP	= "no_hp";
    private static final String COLUMN_VOTE_MESSAGE	= "message";
}
